﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Xk
{
    public partial class frmStudentNotSelectCourse : Form
    {
        public frmStudentNotSelectCourse()
        {
            InitializeComponent();
        }

        private void getClass()
        {
            SqlConnection cn = new SqlConnection(Properties.Settings.Default.XkConnectionString);
            string sql = "SELECT ClassNo='',ClassName='请选择班级'";
            sql += " UNION SELECT ClassNo,ClassName FROM Class";
            sql += " ORDER BY ClassNo";
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            cn.Open();
            da.Fill(ds, "Class");
            cn.Close();
            cbClass.ValueMember = "ClassNo";
            cbClass.DisplayMember = "ClassName";
            cbClass.DataSource = ds.Tables["Class"];
        }

        private void getStudent()
        {
            SqlConnection cn = new SqlConnection(Properties.Settings.Default.XkConnectionString);
            string sql = "SELECT S.*,ClassName FROM Student S,Class C";
            sql += " WHERE S.ClassNo=C.ClassNo";
            sql += " AND StuNo NOT IN (SELECT StuNo FROM StuCou)";
            if (cbClass.SelectedIndex > 0)
                sql += " AND S.ClassNo = @ClassNO";
            sql += " ORDER BY StuNo";
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            if(cbClass.SelectedIndex>0)
                da.SelectCommand.Parameters.Add("ClassNo", SqlDbType.NVarChar, 8).Value = cbClass.SelectedValue.ToString();
            cn.Open();
            da.Fill(ds, "Student");
            cn.Close();
            dataGridView1.DataSource = ds.Tables["Student"];
        }

        private void frmStudentNotSelectCourse_Load(object sender, EventArgs e)
        {
            dataGridView1.AutoGenerateColumns = false;
            getClass();
            getStudent();
        }

        private void cbClass_SelectedIndexChanged(object sender, EventArgs e)
        {
            getStudent();
        }
    }
}
